package com.wanshi.mapper;

import com.wanshi.po.User;
import com.wanshi.po.UserPo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import java.util.List;

public interface UserMapper {

    @Select("select * from user where username=#{username} and password=#{password}")
    User findUserByNameAndPsd(@Param("username") String username,@Param("password") String password);


    //获取所有学生的分数
    @Select(" SELECT id,username,\n" +
            "            IFNULL(( SELECT SUM(degree) FROM timu WHERE id IN\n" +
            "            (SELECT DISTINCT timu_id\n" +
            "            FROM record\n" +
            "            WHERE user_id = user.id\n" +
            "            AND result = 1)),0) score,\n" +
            "            (SELECT MAX(add_time)  FROM record WHERE user_id = user.id AND result = 1) last_time\n" +
            "            FROM user WHERE clsname = #{clsname}\n" +
            "            ORDER BY score DESC\n" +
            "            ,last_time")
    List<UserPo> getScoreListOfCls(String clsname);

    @Update("UPDATE USER SET PASSWORD = #{newpsd} WHERE id = #{user_id};")
    void modifyPsd(@Param("user_id") Integer user_id, @Param("newpsd")String newpsd);

    @Select("SELECT DISTINCT clsname FROM user")
    List<String> getCls();
}
